<!DOCTYPE html>
<html>
<head><meta name="generator" content="Hexo 3.8.0">
  <meta charset="utf-8">
  

  
  <title>Mysql基本操作 | SmileYi</title>
  <meta name="viewport" content="width=device-width, initial-scale=1, maximum-scale=1">
  <meta name="description" content="创建库and删除库12create database `test`;drop database `test`; 创建表and删除表1234567891011create table `user`(	`id` int(10) unsigned not null auto_increment,	`name` varchar(50),	`email` varchar(50),	`sex` smallIn">
<meta name="keywords" content="原创">
<meta property="og:type" content="article">
<meta property="og:title" content="Mysql基本操作">
<meta property="og:url" content="http://smileyi.gitee.io/2019/03/24/Mysql基本操作/index.html">
<meta property="og:site_name" content="SmileYi">
<meta property="og:description" content="创建库and删除库12create database `test`;drop database `test`; 创建表and删除表1234567891011create table `user`(	`id` int(10) unsigned not null auto_increment,	`name` varchar(50),	`email` varchar(50),	`sex` smallIn">
<meta property="og:locale" content="zh-CN">
<meta property="og:updated_time" content="2019-06-22T12:34:43.538Z">
<meta name="twitter:card" content="summary">
<meta name="twitter:title" content="Mysql基本操作">
<meta name="twitter:description" content="创建库and删除库12create database `test`;drop database `test`; 创建表and删除表1234567891011create table `user`(	`id` int(10) unsigned not null auto_increment,	`name` varchar(50),	`email` varchar(50),	`sex` smallIn">
  
    <link rel="alternate" href="/atom.xml" title="SmileYi" type="application/atom+xml">
  
  
    <link rel="icon" href="/favicon.png">
  
  
    <link href="//fonts.googleapis.com/css?family=Source+Code+Pro" rel="stylesheet" type="text/css">
  
  <link rel="stylesheet" href="/css/style.css">
</head>
</html>
<body>
  <div id="container">
    <div id="wrap">
      <header id="header">
  <div id="banner"></div>
  <div id="header-outer" class="outer">
    <div id="header-title" class="inner">
      <h1 id="logo-wrap">
        <a href="/" id="logo">SmileYi</a>
      </h1>
      
        <h2 id="subtitle-wrap">
          <a href="/" id="subtitle">只要学会破罐子破摔 世界就会豁然开朗</a>
        </h2>
      
    </div>
    <div id="header-inner" class="inner">
      <nav id="main-nav">
        <a id="main-nav-toggle" class="nav-icon"></a>
        
          <a class="main-nav-link" href="/">首页</a>
        
          <a class="main-nav-link" href="/archives">归档</a>
        
      </nav>
      <nav id="sub-nav">
        
          <a id="nav-rss-link" class="nav-icon" href="/atom.xml" title="RSS Feed"></a>
        
        <a id="nav-search-btn" class="nav-icon" title="搜索"></a>
      </nav>
      <div id="search-form-wrap">
        <form action="//google.com/search" method="get" accept-charset="UTF-8" class="search-form"><input type="search" name="q" class="search-form-input" placeholder="Search"><button type="submit" class="search-form-submit">&#xF002;</button><input type="hidden" name="sitesearch" value="http://smileyi.gitee.io"></form>
      </div>
    </div>
  </div>
</header>
      <div class="outer">
        <section id="main"><article id="post-Mysql基本操作" class="article article-type-post" itemscope itemprop="blogPost">
  <div class="article-meta">
    <a href="/2019/03/24/Mysql基本操作/" class="article-date">
  <time datetime="2019-03-24T11:59:02.000Z" itemprop="datePublished">2019-03-24</time>
</a>
    
  <div class="article-category">
    <a class="article-category-link" href="/categories/Mysql/">Mysql</a>
  </div>

  </div>
  <div class="article-inner">
    
    
      <header class="article-header">
        
  
    <h1 class="article-title" itemprop="name">
      Mysql基本操作
    </h1>
  

      </header>
    
    <div class="article-entry" itemprop="articleBody">
      
        <h4 id="创建库and删除库"><a href="#创建库and删除库" class="headerlink" title="创建库and删除库"></a>创建库and删除库</h4><figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br></pre></td><td class="code"><pre><span class="line">create database `test`;</span><br><span class="line">drop database `test`;</span><br></pre></td></tr></table></figure>
<h4 id="创建表and删除表"><a href="#创建表and删除表" class="headerlink" title="创建表and删除表"></a>创建表and删除表</h4><figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br></pre></td><td class="code"><pre><span class="line">create table `user`(</span><br><span class="line">	`id` int(10) unsigned not null auto_increment,</span><br><span class="line">	`name` varchar(50),</span><br><span class="line">	`email` varchar(50),</span><br><span class="line">	`sex` smallInt(6),</span><br><span class="line">	`birthday` datetime default null,</span><br><span class="line">	`age` smallInt(6) unsigned,</span><br><span class="line">	primary key (`id`),</span><br><span class="line">	key idx_age (`age`)</span><br><span class="line">) engine=InnoDB default charset=utf8;</span><br><span class="line">drop table `user`</span><br></pre></td></tr></table></figure>
<h4 id="添加字段and修改字段and删除字段"><a href="#添加字段and修改字段and删除字段" class="headerlink" title="添加字段and修改字段and删除字段"></a>添加字段and修改字段and删除字段</h4><figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br></pre></td><td class="code"><pre><span class="line">alter table `user` add column `status` tinyint(3);</span><br><span class="line">alter table `user` change column `status` `qq` int(10);</span><br><span class="line">alter table `user` drop column `qq`;</span><br></pre></td></tr></table></figure>
<h4 id="查看表结构"><a href="#查看表结构" class="headerlink" title="查看表结构"></a>查看表结构</h4><figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br></pre></td><td class="code"><pre><span class="line"># 设置显示字符集</span><br><span class="line">set name utf8;</span><br><span class="line">show create table `user`;</span><br><span class="line">describe `user`;</span><br></pre></td></tr></table></figure>
<h4 id="创建索引"><a href="#创建索引" class="headerlink" title="创建索引"></a>创建索引</h4><figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br></pre></td><td class="code"><pre><span class="line">create index idx_age on user(`age`)</span><br><span class="line">create unique index idx_username on user(`username`)</span><br><span class="line"></span><br><span class="line">alter table user add index idx_age(`age`)</span><br><span class="line">alter table user add unique idx_username(`age`)</span><br></pre></td></tr></table></figure>
<h4 id="删除索引"><a href="#删除索引" class="headerlink" title="删除索引"></a>删除索引</h4><figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br></pre></td><td class="code"><pre><span class="line">drop index idx_age on user</span><br><span class="line">alter table user drop index idx_age</span><br></pre></td></tr></table></figure>
<h4 id="数据导入导出"><a href="#数据导入导出" class="headerlink" title="数据导入导出"></a>数据导入导出</h4><figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br></pre></td><td class="code"><pre><span class="line"># 导出数据</span><br><span class="line">mysqldump -uroot -proot dbname [tbname] &gt; user.sql</span><br><span class="line"></span><br><span class="line"># 导入数据</span><br><span class="line">mysql&gt; source ./user.sql</span><br><span class="line"></span><br><span class="line"># 导入文件数据</span><br><span class="line">mysql&gt; load data local infile &apos;./filename.sql&apos; into table tbname(fed1,fed2...)</span><br></pre></td></tr></table></figure>
<h4 id="性能跟踪"><a href="#性能跟踪" class="headerlink" title="性能跟踪"></a>性能跟踪</h4><figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br></pre></td><td class="code"><pre><span class="line"># 开启性能查看</span><br><span class="line">set @@profiling = 1;</span><br><span class="line"># 查看性能总览</span><br><span class="line">show profiles</span><br><span class="line"># 查看详细性能</span><br><span class="line">show profile for query 2</span><br><span class="line"></span><br><span class="line"># 解释语句性能</span><br><span class="line">explain select * from user where id = 1</span><br></pre></td></tr></table></figure>
<h4 id="修改表配置信息"><a href="#修改表配置信息" class="headerlink" title="修改表配置信息"></a>修改表配置信息</h4><figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br></pre></td><td class="code"><pre><span class="line"># 修改存储引擎</span><br><span class="line">alter table user set engine = &apos;InnoDB&apos;</span><br></pre></td></tr></table></figure>
      
    </div>
    <footer class="article-footer">
      <a data-url="http://smileyi.gitee.io/2019/03/24/Mysql基本操作/" data-id="ck5390nr5000jkeczyhmf1mij" class="article-share-link">分享</a>
      
      
  <ul class="article-tag-list"><li class="article-tag-list-item"><a class="article-tag-list-link" href="/tags/原创/">原创</a></li></ul>

    </footer>
  </div>
  
    
<nav id="article-nav">
  
    <a href="/2019/03/25/Mysql索引的数据结构及算法原理/" id="article-nav-newer" class="article-nav-link-wrap">
      <strong class="article-nav-caption">Newer</strong>
      <div class="article-nav-title">
        
          Mysql索引的数据结构及算法原理
        
      </div>
    </a>
  
  
    <a href="/2019/03/24/PHP常用字符串函数/" id="article-nav-older" class="article-nav-link-wrap">
      <strong class="article-nav-caption">Older</strong>
      <div class="article-nav-title">PHP常用字符串函数</div>
    </a>
  
</nav>

  
</article>

</section>
        
          <aside id="sidebar">
  
    
  <div class="widget-wrap">
    <h3 class="widget-title">分类</h3>
    <div class="widget">
      <ul class="category-list"><li class="category-list-item"><a class="category-list-link" href="/categories/Docker/">Docker</a><span class="category-list-count">1</span></li><li class="category-list-item"><a class="category-list-link" href="/categories/Linux/">Linux</a><span class="category-list-count">2</span></li><li class="category-list-item"><a class="category-list-link" href="/categories/MacOS/">MacOS</a><span class="category-list-count">1</span></li><li class="category-list-item"><a class="category-list-link" href="/categories/Mysql/">Mysql</a><span class="category-list-count">3</span></li><li class="category-list-item"><a class="category-list-link" href="/categories/Nginx/">Nginx</a><span class="category-list-count">2</span></li><li class="category-list-item"><a class="category-list-link" href="/categories/PHP/">PHP</a><span class="category-list-count">2</span></li><li class="category-list-item"><a class="category-list-link" href="/categories/Swoole/">Swoole</a><span class="category-list-count">1</span></li><li class="category-list-item"><a class="category-list-link" href="/categories/算法/">算法</a><span class="category-list-count">2</span></li><li class="category-list-item"><a class="category-list-link" href="/categories/网络/">网络</a><span class="category-list-count">1</span></li></ul>
    </div>
  </div>


  
    
  <div class="widget-wrap">
    <h3 class="widget-title">标签</h3>
    <div class="widget">
      <ul class="tag-list"><li class="tag-list-item"><a class="tag-list-link" href="/tags/原创/">原创</a><span class="tag-list-count">10</span></li><li class="tag-list-item"><a class="tag-list-link" href="/tags/总结/">总结</a><span class="tag-list-count">2</span></li><li class="tag-list-item"><a class="tag-list-link" href="/tags/笔记/">笔记</a><span class="tag-list-count">1</span></li><li class="tag-list-item"><a class="tag-list-link" href="/tags/转载/">转载</a><span class="tag-list-count">2</span></li></ul>
    </div>
  </div>


  
    
  <div class="widget-wrap">
    <h3 class="widget-title">归档</h3>
    <div class="widget">
      <ul class="archive-list"><li class="archive-list-item"><a class="archive-list-link" href="/archives/2020/01/">一月 2020</a><span class="archive-list-count">2</span></li><li class="archive-list-item"><a class="archive-list-link" href="/archives/2019/07/">七月 2019</a><span class="archive-list-count">1</span></li><li class="archive-list-item"><a class="archive-list-link" href="/archives/2019/06/">六月 2019</a><span class="archive-list-count">1</span></li><li class="archive-list-item"><a class="archive-list-link" href="/archives/2019/04/">四月 2019</a><span class="archive-list-count">7</span></li><li class="archive-list-item"><a class="archive-list-link" href="/archives/2019/03/">三月 2019</a><span class="archive-list-count">4</span></li></ul>
    </div>
  </div>


  
    
  <div class="widget-wrap">
    <h3 class="widget-title">最新文章</h3>
    <div class="widget">
      <ul>
        
          <li>
            <a href="/2020/01/05/Swoole之server/">swoole之server</a>
          </li>
        
          <li>
            <a href="/2020/01/02/Certbot-免费的ssl证书/">Certbot--免费的ssl证书</a>
          </li>
        
          <li>
            <a href="/2019/07/13/Nginx限流配置/">Nginx限流配置</a>
          </li>
        
          <li>
            <a href="/2019/06/23/Mysql分区/">Mysql分区</a>
          </li>
        
          <li>
            <a href="/2019/04/15/Rsa算法/">Rsa算法</a>
          </li>
        
      </ul>
    </div>
  </div>

  
</aside>
        
      </div>
      <footer id="footer">
  
  <div class="outer">
    <div id="footer-info" class="inner">
      &copy; 2020 王中艺<br>
      <a href="http://www.beian.miit.gov.cn/">京ICP备18032247号-2</a>
    </div>
  </div>
</footer>
    </div>
    <nav id="mobile-nav">
  
    <a href="/" class="mobile-nav-link">首页</a>
  
    <a href="/archives" class="mobile-nav-link">归档</a>
  
</nav>
    

<script src="//ajax.googleapis.com/ajax/libs/jquery/2.0.3/jquery.min.js"></script>


  <link rel="stylesheet" href="/fancybox/jquery.fancybox.css">
  <script src="/fancybox/jquery.fancybox.pack.js"></script>


<script src="/js/script.js"></script>



  </div>
</body>
</html>